package com.rt.tmpt.service.impl;

import com.googlecode.genericdao.search.Search;
import com.googlecode.genericdao.search.SearchResult;
import com.inspiresoftware.lib.dto.geda.adapter.BeanFactory;
import com.inspiresoftware.lib.dto.geda.assembler.DTOAssembler;
import com.rt.code.generator.freemarker.exec.FmUtils;
import com.rt.easyjava.base.biz1.bean.TmptData;
import com.rt.easyjava.base.biz1.mapper.TmptDataMapper;
import com.rt.easyjava.base.biz1.mapper.TmptDataOptionMapper;
import com.rt.tmpt.bean.A1TemplateColumn;
import com.rt.tmpt.bean.A1TemplateColumnOption;
import com.rt.tmpt.bean.A1TemplateTable;
import com.rt.tmpt.dao.impl.TemplateUtils;
import com.rt.tmpt.dao.intf.*;
import com.rt.tmpt.dto.*;
import com.rt.tmpt.others.pageUtils.PaginationService;
import com.rt.tmpt.service.ITemplateService;
import com.rt.tmpt.service.utils.Column_Constant;
import com.rt.tmpt.service.utils.SpringContextHolder;
import com.rt.tmpt.utils.DateTool;
import com.rt.tmpt.utils.FenAndYuan;
import com.rt.tmpt.utils.JsonMap;
import com.rt.tmpt.utils.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.HibernateException;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.data.domain.Example;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.SQLException;
import java.util.*;

@Service
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
@Slf4j
public class TemplateService implements ITemplateService {


    @Resource
    private IA1TemplateTableDao a1TemplateTableDao;
    @Resource
    private IA1TemplateColumnDao a1TemplateColumnDao;
    //@Resource
    private IA2Menu1Dao menu1Dao;
    //@Resource
    private IA2Menu2Dao menu2Dao;
    //@Resource
    private IA2MenuFunctionDao menuFunctionDao;

    @Resource
    IA2UserDao a2UserDao;
    @Resource
    IA3MenuDao a3MenuDao;
    @Resource
    IA3MenuMyDao a3MenuMyDao;

    @Resource
    private ITemplateDao templateDao;
    @Resource
    private TemplateUtils templateUtils;
    @Resource
    DataLimitService dataLimitService;

    @Resource
    private TmptDataMapper tmptDataMapper;
    @Resource
    private TmptDataOptionMapper tmptDataOptionMapper;

    @Override
    public Result genSql4DynamicDS(String paramJson) throws Exception {

        Result resultObj = new Result();
        Map<String, Object> resultMap = null;
        try {
            Map<String, String[]> paramMap = JsonMap.json2Map(paramJson);
            //
            String keyRandom = paramMap.get("_keyRandom")[0];
            //查询历史数据
            String _historyId = null;
            if (paramMap.get("_historyId") != null) {
                _historyId = paramMap.get("_historyId")[0];
            }

            A1TemplateTable a1TemplateTable = new A1TemplateTable();
            a1TemplateTable.setKeyRandom(keyRandom);
            Optional<A1TemplateTable> optionalA1TemplateTable = a1TemplateTableDao.findOne(Example.of(a1TemplateTable));
            A1TemplateTable templateTable = optionalA1TemplateTable.get();
            //加载数据源
            int mode = templateTable.getMode();
            //if(mode==0 || mode==1 || mode==3)
            {
                //SQL拼接,调用自定义SQL Table Name
                resultObj = templateDao.queryColumnAliasAndProperty(templateTable);
                Map<String, String> columnAliasMap
                        = (Map<String, String>) resultObj.getMap().get("columnAliasMap");
                Map<String, Map<String, String>> dbcolumnsProperty
                        = (Map<String, Map<String, String>>) resultObj.getMap().get("dbcolumnsProperty");

                //列所属表名
                Result result1 = templateUtils.queryRalationTableList(templateTable);
                Map<String, String> columnTableMap = (Map<String, String>) result1.getMap().get("columnTableMap");
                resultObj.getMap().put("columnTableMap", columnTableMap);
            }
			/*if(mode==2){
				//调用自定义SQL
				resultObj = templateDao.templateSearchBySql(templateTable,paramMap);
			}
			if(mode==3){
				//调用spring bean 来查询数据
				resultObj = invokeBean(beanObject,beanMethod,paramJson,_historyId,beanHistoryMethod);
			}
			if(mode==4){ //存储过程
				resultObj = templateDao.templateSearchByProcedure(templateTable, paramMap);
			}*/
            return resultObj;

        } catch (Exception e) {
            e.printStackTrace();
            return resultObj.fault(e.getLocalizedMessage());
        }

    }

    /**
     * 动态sql查询
     *
     * @return
     */
    @Override
    public Result paginationBySearch(Map<String, String[]> parameterMap, PageDto pageDto) {
        Result resultObj = new Result();
        try {
            String keyRandom = parameterMap.get("_keyRandom")[0];
            A1TemplateTable a1TemplateTable = new A1TemplateTable();
            a1TemplateTable.setKeyRandom(keyRandom);
            Optional<A1TemplateTable> optionalA1TemplateTable = a1TemplateTableDao.findOne(Example.of(a1TemplateTable));
            if (!optionalA1TemplateTable.isPresent()) {
                return resultObj.fault("无符合要求的模板，请检查菜单URL配置是否正确");
            }
            A1TemplateTable templateTable = optionalA1TemplateTable.get();

            //调用自定义SQL Table Name
            resultObj = templateDao.templateSearchByTableNameSql(templateTable, parameterMap, pageDto);

            return resultObj;

        } catch (Exception e) {
            e.printStackTrace();
            return resultObj.fault(e.getLocalizedMessage());
        }

    }


    /**
     * 事务关闭，通过a1TemplateTable来确认，取哪个DB
     *
     * @param templateTable
     * @param paramMap
     * @param paramJson
     * @param _historyId
     * @param paramMap      做数据边界权限时新加的参数，如：userId,menu2Id。。。
     * @return
     * @throws Exception
     */
    @Deprecated
    @Override
    public Result paginationBySearch4DynamicDS(A1TemplateTable templateTable,
                                               Map<String, String[]> paramMap1, String paramJson,
                                               String _historyId, Map<String, Object> paramMap) throws Exception {
        Result resultObj = new Result();
        //加载数据源
        String beanObject = templateTable.getBeanObject();
        String beanMethod = templateTable.getBeanMethod();
        String beanHistoryMethod = templateTable.getBeanHistoryMethod();

        //数据边界控制
        //取当前用户访问的二级菜单的最高权限数据边界值
        Map<String, Object> paramMapDataLimit = dataLimitService.getLimitParam(paramMap);
        String dataLimitType = String.valueOf(paramMapDataLimit.get("dataLimitType"));
        String userId = String.valueOf(paramMapDataLimit.get("userId"));
        String companyId = String.valueOf(paramMapDataLimit.get("companyId"));
        String[] params = {dataLimitType, userId, companyId};

		/*int mode = templateTable.getMode();
		if(mode==0){
			//SQL拼接
			resultObj = templateDao.templateSearch(templateTable,paramMap1,params);
		}
		if(mode==1){
			//调用自定义SQL Table Name
			//resultObj = templateDao.templateSearchByTableNameSql(templateTable,paramMap1,params);
		}
		if(mode==2){
			//调用自定义SQL
			resultObj = templateDao.templateSearchBySql(templateTable,paramMap1);
		}
		if(mode==3){
			//调用spring bean 来查询数据
			resultObj = invokeBean(beanObject,beanMethod,paramJson,_historyId,beanHistoryMethod);
		}
		if(mode==4){//存储过程
			resultObj = templateDao.templateSearchByProcedure(templateTable, paramMap1);
		}*/
        return resultObj;
    }

    /**
     * 调用spring bean 来查询数据
     *
     * @param beanObject
     * @param beanMethod
     * @param paramJson
     * @param _historyId
     * @param beanHistoryMethod
     * @return
     */
    private Result invokeBean(String beanObject, String beanMethod, String paramJson, String _historyId, String beanHistoryMethod) {
        Result resultObj = new Result();
        Map<String, Object> resultMap = null;
        //重新解释页面传过来的参数
        Map<String, Object> paramMap1 = JsonMap.json2Map1(paramJson);
        try {
            //调用自定义的查询历史记录bean
            if (_historyId != null && !StringUtils.isNull(beanHistoryMethod)) {
                //动态调用bean
                resultObj = invokeBean(beanObject, beanHistoryMethod, paramMap1);
            } else {
                //动态调用bean
                resultObj = invokeBean(beanObject, beanMethod, paramMap1);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return resultObj.fault(e.getLocalizedMessage());
        }

        return resultObj.success();
    }

    /**
     * 调用spring方法
     *
     * @param beanObject
     * @param beanMethod
     * @param paramMap
     * @return
     * @throws NoSuchMethodException
     * @throws SecurityException
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     * @throws InvocationTargetException
     */
    private Result invokeBean(String beanObject, String beanMethod, Map<String, Object> paramMap) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        Object o = SpringContextHolder.getBean(beanObject);
        Method method = o.getClass().getMethod(beanMethod, Map.class);
        return (Result) method.invoke(o, paramMap);
    }

    private Result invokeBean(String beanObject, String beanMethod, List<Map<String, Object>> list) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        Object o = SpringContextHolder.getBean(beanObject);
        Method method = o.getClass().getMethod(beanMethod, List.class);
        return (Result) method.invoke(o, list);
    }


    @Override
    public A1TemplateTableDto getTemplateTable(String keyRandom) {
        A1TemplateTable a1TemplateTable = new A1TemplateTable();
        a1TemplateTable.setKeyRandom(keyRandom);
        Optional<A1TemplateTable> optionalA1TemplateTable = a1TemplateTableDao.findOne(Example.of(a1TemplateTable));
        if (!optionalA1TemplateTable.isPresent()) {
            return null;
        }
        A1TemplateTable entity = optionalA1TemplateTable.get();

        A1TemplateTableDto dto = new A1TemplateTableDto();
        DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateTableDto.class);

        assembler.assembleDto(dto, entity, null, new BeanFactory() {
            @Override
            public Object get(String bean) {
                if ("a1TemplateColumnDto".equals(bean)) {
                    return new A1TemplateColumnDto();
                }
                return null;
            }
        });
        //多值的列
        List<A1TemplateColumnDto> a1TemplateColumnDtoList = new ArrayList<A1TemplateColumnDto>();
        //将dataResoure=1，数据字典中数据，加载到column的option中
        List<A1TemplateColumn> columnList = entity.getA1TemplateColumnList();
        templateUtils.loadColumnOptionList(columnList);

        for (A1TemplateColumn column : columnList) {
            A1TemplateColumnDto a1TemplateColumnDto = new A1TemplateColumnDto();
            DTOAssembler columnDtoAss = DTOAssembler.newAssembler(A1TemplateColumnDto.class);
            columnDtoAss.assembleDto(a1TemplateColumnDto, column, null, null);

            if (column.getA1TemplateColumnOptionList().size() > 0) {
                for (A1TemplateColumnOption option : column.getA1TemplateColumnOptionList()) {
                    int deleteFlag = option.getDeleteFlag() == null ? 0 : option.getDeleteFlag().intValue();
                    if (deleteFlag == 1) continue;
                    String keyName = option.getKeyName();
                    String valueName = option.getValueName();
                    String optionName = option.getOptionName();
                    A1TemplateColumnOptionDto a1TemplateColumnOptionDto = new A1TemplateColumnOptionDto();
                    a1TemplateColumnOptionDto.setId(option.getId());
                    a1TemplateColumnOptionDto.setKeyName(keyName);
                    a1TemplateColumnOptionDto.setValueName(valueName);
                    a1TemplateColumnOptionDto.setOptionName(optionName);
                    a1TemplateColumnDto.getA1TemplateColumnOptionDtoList().add(a1TemplateColumnOptionDto);
                }
            }

            if (column.getDataResource() != null &&
                    (column.getDataResource() == A1TemplateColumnDto.dataResource_Sql
                            || column.getDataResource() == A1TemplateColumnDto.dataResource_Page)) {
                String optionsSql = column.getOptionsSql();
                if (!StringUtils.isNull(optionsSql)) {
                    String columnName = column.getColumnName();
                    String defaultValue = column.getDefaultValue() == null ? "" : column.getDefaultValue();
                    a1TemplateColumnDto.setColumnName(columnName);
                    a1TemplateColumnDto.setDefaultValue(defaultValue);
                    //sql 查询的表 可能是 业务数据库表，根据模板中的dataSource来区别
                    List<Map<String, Object>> list = templateDao.queryReportBySearch(optionsSql);
                    for (Map<String, Object> map : list) {
                        String keyName = String.valueOf(map.get("id"));
                        String valueName = String.valueOf(map.get("name"));
                        //int isDefault = 0;//option.getIsDefault();
                        A1TemplateColumnOptionDto a1TemplateColumnOptionDto = new A1TemplateColumnOptionDto();
                        a1TemplateColumnOptionDto.setKeyName(keyName);
                        a1TemplateColumnOptionDto.setValueName(valueName);
                        //a1TemplateColumnOptionDto.setIsDefault(isDefault);
                        a1TemplateColumnDto.getA1TemplateColumnOptionDtoList().add(a1TemplateColumnOptionDto);
                    }
                }
            }

            //默认的UI是 "easyui-textbox"
            if (StringUtils.isNull(column.getColumnType())) {
                column.setColumnType("easyui-textbox");
            }

            a1TemplateColumnDtoList.add(a1TemplateColumnDto);
        }
        dto.setA1TemplateColumnDtoList(a1TemplateColumnDtoList);

        return dto;
    }

    /**
     * 多值选择，key-value形式; case status when 1 then '有效' when 0 then '无效' end
     *
     * @param keyRandom
     * @return
     */
    @Override
    public String getTemplateTableColumnOptionJson(String keyRandom) {
		/*Search search = new Search();
		search.addFilterEqual("keyRandom", keyRandom);
		A1TemplateTable a1TemplateTable = (A1TemplateTable)a1TemplateTableDao.searchUnique(search);
*/
        A1TemplateTable a1TemplateTable = new A1TemplateTable();
        a1TemplateTable.setKeyRandom(keyRandom);
        A1TemplateTable templateTable = a1TemplateTableDao.findOne(Example.of(a1TemplateTable)).get();
        //多值的列，拼成json返回给页面
        JSONArray jsonArrayColumnOptions = new JSONArray();
        for (A1TemplateColumn column : a1TemplateTable.getA1TemplateColumnList()) {
            int dataResource = column.getDataResource();
            if (dataResource == A1TemplateColumnDto.dataResource_OptionsTable
                    || dataResource == A1TemplateColumnDto.dataResource_OptionsTableKeyEqualValue) {
                getTemplateTableColumnOptionJson_OptionsTable(column, jsonArrayColumnOptions);
            }
            if (dataResource == A1TemplateColumnDto.dataResource_ColumnOptionData) {
                //getTemplateTableColumnOptionJson_OptionDataTable(column, jsonArrayColumnOptions);
            }
            if (dataResource == A1TemplateColumnDto.dataResource_Sql
                    || dataResource == A1TemplateColumnDto.dataResource_Page) {
                getTemplateTableColumnOptionJson_Sql(column, jsonArrayColumnOptions, a1TemplateTable);
            }
        }
        String jsonArrayColumnOptionsStr = jsonArrayColumnOptions.toString();
        return jsonArrayColumnOptionsStr;
    }

    private void getTemplateTableColumnOptionJson_OptionsTable(A1TemplateColumn column, JSONArray jsonArrayColumnOptions) {
        if (column.getA1TemplateColumnOptionList().size() > 0) {
            //json options
            JSONObject jsonObjectColumn = new JSONObject();
            jsonObjectColumn.put("columnName", column.getColumnName());
            jsonObjectColumn.put("defaultValue", column.getDefaultValue() == null ? "" : column.getDefaultValue());
            jsonObjectColumn.put("propertyName", StringUtils.switch2Property(column.getColumnName()));
            JSONArray jsonArrayOptions = new JSONArray();
            for (A1TemplateColumnOption option : column.getA1TemplateColumnOptionList()) {
                if (option.getDeleteFlag() == 1) continue;
                String keyName = option.getKeyName();
                String valueName = option.getValueName();
                //String isDefault=String.valueOf(option.getIsDefault());
                //json options
                JSONObject jsonObjectOption = new JSONObject();
                //jsonObjectOption.put(keyName, valueName);
                jsonObjectOption.put("keyName", keyName);
                jsonObjectOption.put("valueName", valueName);
                //jsonObjectOption.put("isDefault", isDefault);
                jsonObjectOption.put("optionName", option.getOptionName());
                jsonArrayOptions.put(jsonObjectOption);
            }
            jsonObjectColumn.put("columnOptions", jsonArrayOptions);
            jsonArrayColumnOptions.put(jsonObjectColumn);
        }
    }


	/*private void getTemplateTableColumnOptionJson_OptionDataTable(A1TemplateColumn column, JSONArray jsonArrayColumnOptions){
		//json options
		String columnName = column.getColumnName();
		JSONObject jsonObjectColumn = new JSONObject();
		jsonObjectColumn.put("columnName", columnName);
		jsonObjectColumn.put("defaultValue", column.getDefaultValue() == null ? "" : column.getDefaultValue());
		jsonObjectColumn.put("propertyName", StringUtils.switch2Property(columnName));
		JSONArray jsonArrayOptions = new JSONArray();

		//查询该列的数据字典
		A1TemplateColumnOptionData a1TemplateColumnOptionData = new A1TemplateColumnOptionData();
		a1TemplateColumnOptionData.setColumnName(columnName);
		List<A1TemplateColumnOptionData> optionDataList = optionDataDao.findAll(Example.of(a1TemplateColumnOptionData), Sort.by(Sort.Direction.DESC,"sort"));

		for(A1TemplateColumnOptionData optionData : optionDataList){
			if(optionData.getDeleteFlag()==1)continue;
			long optionId = 0L;
			String keyName = optionData.getKeyName();
			String valueName=optionData.getValueName();
			String optionName=optionData.getOptionName();
			//json options
			JSONObject jsonObjectOption = new JSONObject();
			//jsonObjectOption.put(keyName, valueName);
			jsonObjectOption.put("keyName", keyName);
			jsonObjectOption.put("valueName", valueName);
			//jsonObjectOption.put("isDefault", 0);
			jsonObjectOption.put("optionName", optionName);
			jsonArrayOptions.put(jsonObjectOption);
		}
		jsonObjectColumn.put("columnOptions",jsonArrayOptions);
		jsonArrayColumnOptions.put(jsonObjectColumn);
	}*/


    private void getTemplateTableColumnOptionJson_Sql(A1TemplateColumn column, JSONArray jsonArrayColumnOptions, A1TemplateTable a1TemplateTable) {
        String optionsSql = column.getOptionsSql();
        if (!StringUtils.isNull(optionsSql)) {
            JSONObject jsonObjectColumn = new JSONObject();
            jsonObjectColumn.put("columnName", column.getColumnName());
            jsonObjectColumn.put("defaultValue", column.getDefaultValue() == null ? "" : column.getDefaultValue());
            jsonObjectColumn.put("propertyName", StringUtils.switch2Property(column.getColumnName()));
            JSONArray jsonArrayOptions = new JSONArray();

            List<Map<String, Object>> list = templateDao.queryReportBySearch(optionsSql);
            for (Map<String, Object> map : list) {
                String keyName = String.valueOf(map.get("keyName"));
                String valueName = String.valueOf(map.get("valueName"));
                //String isDefault=String.valueOf(option.getIsDefault());
                //json options
                JSONObject jsonObjectOption = new JSONObject();
                //jsonObjectOption.put(keyName, valueName);
                jsonObjectOption.put("keyName", keyName);
                jsonObjectOption.put("valueName", valueName);
                //jsonObjectOption.put("isDefault", "0");
                jsonArrayOptions.put(jsonObjectOption);

            }
            jsonObjectColumn.put("columnOptions", jsonArrayOptions);
            jsonArrayColumnOptions.put(jsonObjectColumn);

        }
    }

    /**
     * 多值选择，key-value形式; case status when 1 then '有效' when 0 then '无效' end
     *
     * @param keyRandom
     * @return
     */
    @Override
    public List<A1TemplateColumnDto> getTemplateTableColumnOptionList(String keyRandom) {
        A1TemplateTable a1TemplateTable = new A1TemplateTable();
        a1TemplateTable.setKeyRandom(keyRandom);
        A1TemplateTable templateTable = a1TemplateTableDao.findOne(Example.of(a1TemplateTable)).get();

        int xx = templateTable.getA1TemplateColumnList().size();

        //多值的列，拼成json返回给页面
        List<A1TemplateColumnDto> a1TemplateColumnDtoList = new ArrayList<>();
        //通过配置的options 表来加载数据
        loadTemplateTableColumnOptionListFromOptionsTable(templateTable, a1TemplateColumnDtoList);
        //从加载的SQL语句来读新的表加载数据
        loadTemplateTableColumnOptionListFromSql(templateTable, a1TemplateColumnDtoList);

        //多值的列，加入json返回
        for (A1TemplateColumnDto columnDto : a1TemplateColumnDtoList) {
            JSONArray ja = new JSONArray();
            for (A1TemplateColumnOptionDto optionDto : columnDto.getA1TemplateColumnOptionDtoList()) {
                JSONObject jo = new JSONObject();
                jo.put("optionId", optionDto.getId());
                jo.put("valueName", optionDto.getValueName());
                jo.put("keyName", optionDto.getKeyName());
                jo.put("optionName", optionDto.getOptionName());
                ja.put(jo);
            }
            columnDto.setA1TemplateColumnOptionJsonArray(ja.toString());
        }

        return a1TemplateColumnDtoList;
    }


    /**
     * 通过OptionId 查找 option hidden column
     * @param optionId
     * @return
     *//*
	@Override
	public List<A1TemplateColumnOptionHidColDto> queryOptionHidColByOptionId(long optionId){
		*//*Search search = new Search();
		search.addFilterEqual("deleteFlag", 0);
		search.addFilterEqual("a1TemplateColumnOption.id", optionId);*//*

		A1TemplateColumnOption a1TemplateColumnOption  = new A1TemplateColumnOption();
		a1TemplateColumnOption.setId(optionId);

		A1TemplateColumnOptionHidCol a1TemplateColumnOptionHidCol = new A1TemplateColumnOptionHidCol();
		a1TemplateColumnOptionHidCol.setDeleteFlag(0);
		a1TemplateColumnOptionHidCol.setA1TemplateColumnOption(a1TemplateColumnOption);
		List<A1TemplateColumnOptionHidCol> a1TemplateColumnOptionHidColList = optionHidColDao.findAll(Example.of(a1TemplateColumnOptionHidCol));

		//List<A1TemplateColumnOptionHidCol> a1TemplateColumnOptionHidColList = optionHidColDao.search(search);
		List<A1TemplateColumnOptionHidColDto> a1TemplateColumnOptionHidColDtoList = new ArrayList<A1TemplateColumnOptionHidColDto>();

		//Assembler
		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateColumnOptionHidColDto.class);
		assembler.assembleDtos(a1TemplateColumnOptionHidColDtoList, a1TemplateColumnOptionHidColList, null, null);

		return a1TemplateColumnOptionHidColDtoList;
	}
*/

    /**
     * 从加载的SQL语句来读新的表加载数据
     *
     * @param a1TemplateTable
     * @param a1TemplateColumnDtoList
     * @return
     */
    private void loadTemplateTableColumnOptionListFromSql(A1TemplateTable a1TemplateTable, List<A1TemplateColumnDto> a1TemplateColumnDtoList) {
        for (A1TemplateColumn column : a1TemplateTable.getA1TemplateColumnList()) {
            int dataResource = column.getDataResource() == null ? 0 : column.getDataResource().intValue();
            if (dataResource == A1TemplateColumnDto.dataResource_Sql) {
                String optionsSql = column.getOptionsSql();
                if (!StringUtils.isNull(optionsSql)) {
                    String columnName = column.getColumnName();
                    A1TemplateColumnDto a1TemplateColumnDto = new A1TemplateColumnDto();
                    a1TemplateColumnDto.setColumnName(columnName);

                    DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateColumnDto.class);
                    assembler.assembleDto(a1TemplateColumnDto, column, null, null);

                    List<Map<String, Object>> list = templateDao.queryReportBySearch(optionsSql);
                    for (Map<String, Object> map : list) {
                        String keyName = String.valueOf(map.get("keyName"));
                        String valueName = String.valueOf(map.get("valueName"));
                        A1TemplateColumnOptionDto a1TemplateColumnOptionDto = new A1TemplateColumnOptionDto();
                        a1TemplateColumnOptionDto.setKeyName(keyName);
                        a1TemplateColumnOptionDto.setValueName(valueName);
                        a1TemplateColumnDto.getA1TemplateColumnOptionDtoList().add(a1TemplateColumnOptionDto);
                    }
                    a1TemplateColumnDtoList.add(a1TemplateColumnDto);

                }
            }
        }
    }

    /**
     * 从 A1TemplateColumnOption 表中加载数据
     *
     * @param a1TemplateTable
     * @param a1TemplateColumnDtoList
     * @return
     */
    private void loadTemplateTableColumnOptionListFromOptionsTable(A1TemplateTable a1TemplateTable, List<A1TemplateColumnDto> a1TemplateColumnDtoList) {
        for (A1TemplateColumn column : a1TemplateTable.getA1TemplateColumnList()) {
            int dataResource = column.getDataResource() == null ? 0 : column.getDataResource().intValue();
            if (dataResource == A1TemplateColumnDto.dataResource_OptionsTable
                    || dataResource == A1TemplateColumnDto.dataResource_OptionsTableKeyEqualValue) {
                if (column.getA1TemplateColumnOptionList().size() > 0) {
                    String columnName = column.getColumnName();
                    A1TemplateColumnDto a1TemplateColumnDto = new A1TemplateColumnDto();
                    a1TemplateColumnDto.setColumnName(columnName);

                    DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateColumnDto.class);
                    assembler.assembleDto(a1TemplateColumnDto, column, null, null);

                    for (A1TemplateColumnOption option : column.getA1TemplateColumnOptionList()) {
                        if (option.getDeleteFlag() == 1) continue;
                        long optionId = option.getId();
                        String keyName = option.getKeyName();
                        String valueName = option.getValueName();
                        String optionName = option.getOptionName();
                        A1TemplateColumnOptionDto a1TemplateColumnOptionDto = new A1TemplateColumnOptionDto();
                        a1TemplateColumnOptionDto.setId(optionId);
                        a1TemplateColumnOptionDto.setKeyName(keyName);
                        a1TemplateColumnOptionDto.setValueName(valueName);
                        a1TemplateColumnOptionDto.setOptionName(optionName);
                        a1TemplateColumnDto.getA1TemplateColumnOptionDtoList().add(a1TemplateColumnOptionDto);
                    }
                    a1TemplateColumnDtoList.add(a1TemplateColumnDto);
                }
            }
        }
    }



    @Override
    public List<Map<String, Object>> queryReportBySearch(A1TemplateTableDto templateTableDto, String reportSqlString, List<Object> paramList) {

        A1TemplateTable a1TemplateTable = new A1TemplateTable();
        a1TemplateTable.setKeyRandom(templateTableDto.getKeyRandom());
        //加载数据源
        return templateDao.queryListBySearch(reportSqlString, paramList);
    }


    @Override
    public Result addA1TemplateTable(A1TemplateTableDto a1TemplateTableDto) {
        Result result = new Result();
        A1TemplateTable a1TemplateTable = new A1TemplateTable();
        DTOAssembler ass = DTOAssembler.newAssembler(A1TemplateTableDto.class);
        ass.assembleEntity(a1TemplateTableDto, a1TemplateTable, null, null);
        Map<String, Object> tableInfo = templateDao.selectTableInfo(a1TemplateTable.getTableName());
        a1TemplateTable.setComment(parseTableComment(tableInfo)); // comment
        a1TemplateTable = a1TemplateTableDao.save(a1TemplateTable);
        result.getMap().put("a1TemplateTableId", a1TemplateTable.getId());
        return result.success();
    }

    private String parseTableComment(Map<String, Object> tableInfo) {
        Object tableComment = tableInfo.get("table_comment");
        if (tableComment==null) {
            return "";
        }
        if (StringUtils.isNull(tableComment.toString())) {
            return "";
        }
        return tableComment.toString();
    }


    @Override
    public Result addA1TemplateColumn2Table(A1TemplateColumnDto a1TemplateColumnDto, long tableId) {
        Result result = new Result();
        A1TemplateColumn a1TemplateColumn = new A1TemplateColumn();
        DTOAssembler ass = DTOAssembler.newAssembler(A1TemplateColumnDto.class);
        ass.assembleEntity(a1TemplateColumnDto, a1TemplateColumn, null, null);
        a1TemplateColumn.setA1TemplateTable(a1TemplateTableDao.findById(tableId).get());
        a1TemplateColumnDao.save(a1TemplateColumn);
        return result.success();
    }

    @Override
    public PaginationDto<A1TemplateTableDto> paginationA1TemplateTableListBySearch(Search search) {
        PaginationService<A1TemplateTableDto> paginationService = new PaginationService<A1TemplateTableDto>(search);
        if (!paginationService.validataPageSize()) {
            return null;
        }
        ;
        SearchResult<A1TemplateTable> searchResult = null;//?????????a1TemplateTableDao.searchAndCount(search);
        List<A1TemplateTable> entityList = searchResult.getResult();

        int totalCount = searchResult.getTotalCount();
        final List<A1TemplateTableDto> dtoList = new ArrayList<A1TemplateTableDto>();

        DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateTableDto.class);
        assembler.assembleDtos(dtoList, entityList, null, null);
        paginationService.assemblePaginationDto(dtoList, totalCount);

        return paginationService.getPaginationDto();
    }


    @Override
    public PaginationDto<A1TemplateColumnDto> paginationA1TemplateColumnListBySearch(Search search) {
        PaginationService<A1TemplateColumnDto> paginationService = new PaginationService<A1TemplateColumnDto>(search);
        if (!paginationService.validataPageSize()) {
            return null;
        }
        ;
        SearchResult<A1TemplateColumn> searchResult = null;//????????? a1TemplateColumnDao.searchAndCount(search);
        List<A1TemplateColumn> entityList = searchResult.getResult();

        int totalCount = searchResult.getTotalCount();
        final List<A1TemplateColumnDto> dtoList = new ArrayList<A1TemplateColumnDto>();

        DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateColumnDto.class);
        assembler.assembleDtos(dtoList, entityList, null, null);
        paginationService.assemblePaginationDto(dtoList, totalCount);

        return paginationService.getPaginationDto();
    }


    /**
     * 添加数据
     *
     * @param keyRandom
     * @param jsonParam
     * @return
     * @throws SQLException
     * @throws HibernateException
     */
    @Override
    public Result addData(String keyRandom, String jsonParam) throws HibernateException, SQLException {
        Result result = new Result();
        try {
            JSONObject json = new JSONObject(jsonParam);

            A1TemplateTable a1TemplateTableParam = new A1TemplateTable();
            a1TemplateTableParam.setKeyRandom(keyRandom);
            A1TemplateTable a1TemplateTable = a1TemplateTableDao.findOne(Example.of(a1TemplateTableParam)).get();

            //取要添加和编辑的列
            List<Map<String, Object>> columnList = getColumnList(a1TemplateTable, json);

            result = addData4DynamicDS(a1TemplateTable, json, columnList);
        } catch (Exception e) {
            e.printStackTrace();
            return result.fault(e.getLocalizedMessage());
        }
        return result;
    }

    /**
     * 向指定的数据库表中insert
     *
     * @param a1TemplateTable
     * @param json
     * @param columnList
     * @return
     * @throws Exception
     */
    @Override
    public Result addData4DynamicDS(A1TemplateTable a1TemplateTable, JSONObject json, List<Map<String, Object>> columnList) throws Exception {
        Result result = new Result();

        //create man id
        String value = json.optJSONArray("creatorId").get(0).toString();
        Map map = new HashMap();
        map.put(Column_Constant.COLUMN_NAME, "creator_id");
        map.put(Column_Constant.COLUMN_VALUE, value);
        columnList.add(map);
        //create man
        value = json.optJSONArray("creator").get(0).toString();
        map = new HashMap();
        map.put(Column_Constant.COLUMN_NAME, "creator");
        map.put(Column_Constant.COLUMN_VALUE, value);
        columnList.add(map);
        //companyId
        value = json.optJSONArray("companyId").get(0).toString();
        map = new HashMap();
        map.put(Column_Constant.COLUMN_NAME, "company_id");
        map.put(Column_Constant.COLUMN_VALUE, value);
        columnList.add(map);
        //create date
        map = new HashMap();
        map.put(Column_Constant.COLUMN_NAME, "create_time");
        map.put(Column_Constant.COLUMN_VALUE, new java.sql.Timestamp(new Date().getTime()));
        map.put(Column_Constant.DATA_TYPE, "datetime");
        columnList.add(map);

        //加入intercept
        Result interceptResult = intercept4Add(a1TemplateTable, columnList);
        if (interceptResult.isSuccess() && "continue".equals(interceptResult.getRemark())) {
            templateDao.addData(a1TemplateTable.getTableName(), columnList);
            return result.success();
        } else {
            return interceptResult;
        }
    }


    /**
     * 编辑数据
     *
     * @param keyRandom
     * @param id
     * @param jsonParam
     * @return
     */
    @Override
    public Result editData(String keyRandom, String id, String jsonParam) throws HibernateException, SQLException {
        Result result = new Result();
        try {
            JSONObject json = new JSONObject(jsonParam);

            //查询表名
            A1TemplateTable a1TemplateTableParam = new A1TemplateTable();
            a1TemplateTableParam.setKeyRandom(keyRandom);
            A1TemplateTable a1TemplateTable = a1TemplateTableDao.findOne(Example.of(a1TemplateTableParam)).get();

            //取要添加和编辑的列
            List<Map<String, Object>> columnList = getColumnList(a1TemplateTable, json);

            //result = templateService.editData4DynamicDS(a1TemplateTable, id, json, columnList);
            result = editData4DynamicDS(a1TemplateTable, id, json, columnList);
        } catch (Exception e) {
            e.printStackTrace();
            return result.fault(e.getLocalizedMessage());
        }
        return result;
    }

    @Override
    public Result editData4DynamicDS(A1TemplateTable a1TemplateTable, String id, JSONObject json, List<Map<String, Object>> columnList) throws Exception {
        Result result = new Result();

        //modify man id
        String value = json.optJSONArray("creatorId").get(0).toString();
        Map map = new HashMap();
        map.put(Column_Constant.COLUMN_NAME, "updator_id");
        map.put(Column_Constant.COLUMN_VALUE, value);
        columnList.add(map);
        //modify man
        value = json.optJSONArray("creator").get(0).toString();
        map = new HashMap();
        map.put(Column_Constant.COLUMN_NAME, "updator");
        map.put(Column_Constant.COLUMN_VALUE, value);
        columnList.add(map);
        //modify date
        map = new HashMap();
        map.put(Column_Constant.COLUMN_NAME, "update_time");
        map.put(Column_Constant.COLUMN_VALUE, new java.sql.Timestamp(new Date().getTime()));
        map.put(Column_Constant.DATA_TYPE, "datetime");
        columnList.add(map);

        //加入intercept
        Result interceptResult = intercept4Edit(a1TemplateTable, columnList, id);
        if (interceptResult.isSuccess() && "continue".equals(interceptResult.getRemark())) {
            templateDao.editData(a1TemplateTable.getTableName(), id, columnList);
        } else {
            return interceptResult;
        }

        return result.success();
    }

    @Override
    public Result removeData(String... parameters) {
        Result result = new Result();
        String _keyRandom = parameters[0];
        String _id = parameters[1];
        String createMan = parameters[2];

        A1TemplateTable a1TemplateTableParam = new A1TemplateTable();
        a1TemplateTableParam.setKeyRandom(_keyRandom);
        A1TemplateTable a1TemplateTable = a1TemplateTableDao.findOne(Example.of(a1TemplateTableParam)).get();

        //update 列数据
        List<Map<String, Object>> columnList = new ArrayList<>();
        Map<String, Object> map = new HashMap<String, Object>();
        map.put(Column_Constant.COLUMN_NAME, "delete_flag");
        map.put(Column_Constant.COLUMN_VALUE, "1");
        map.put(Column_Constant.DATA_TYPE, "int");
        columnList.add(map);
        //modify man
        String value = createMan;
        map = new HashMap();
        map.put(Column_Constant.COLUMN_NAME, "updator");
        map.put(Column_Constant.COLUMN_VALUE, value);
        columnList.add(map);
        //modify date
        map = new HashMap();
        map.put(Column_Constant.COLUMN_NAME, "update_time");
        map.put(Column_Constant.COLUMN_VALUE, new java.sql.Timestamp(new Date().getTime()));
        map.put(Column_Constant.DATA_TYPE, "datetime");
        columnList.add(map);

        //动态数据源
        try {
            templateDao.editData(a1TemplateTable.getTableName(), _id, columnList);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("删除数据出错");
        }
        return result.success();
    }


    /**
     * intercept invoke my own spring method when add or edit data
     *
     * @param a1TemplateTable
     * @return
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     * @throws SecurityException
     * @throws NoSuchMethodException
     */
    private Result intercept4Add(A1TemplateTable a1TemplateTable, List<Map<String, Object>> columnList) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        Result result = new Result();
        //interceptObject,interceptMethod
        String interceptObject = a1TemplateTable.getInterceptObject();
        String interceptMethod = a1TemplateTable.getInterceptAddMethod();

        if (StringUtils.isNull(interceptObject) || StringUtils.isNull(interceptMethod)) {
            result.setRemark("continue");
            return result.success();
        }
        //调用bean
        Result invodeBeanResult = invokeBean(interceptObject, interceptMethod, columnList);

        return invodeBeanResult;
    }

    private Result intercept4Edit(A1TemplateTable a1TemplateTable, List<Map<String, Object>> columnList, String id) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        Result result = new Result();
        //interceptObject,interceptMethod
        String interceptObject = a1TemplateTable.getInterceptObject();
        String interceptMethod = a1TemplateTable.getInterceptEditMethod();

        if (StringUtils.isNull(interceptObject) || StringUtils.isNull(interceptMethod)) {
            result.setRemark("continue");
            return result.success();
        }
        //调用bean
        Result invodeBeanResult = invokeBean(interceptObject, interceptMethod, columnList);

        return invodeBeanResult;
    }

    /**
     * 取模板中要添加和编辑的列
     *
     * @param a1TemplateTable
     * @return
     */
    private List<Map<String, Object>> getColumnList(A1TemplateTable a1TemplateTable, JSONObject json) {

        //取出每一列的data_tranfer
        Map<String, A1TemplateColumn> a1TemplateColumnMap = templateUtils.transferA1TemplateColumnMap(a1TemplateTable);
        List<A1TemplateColumn> a1TemplateColumnList1 = a1TemplateTable.getA1TemplateColumnList();
        List<Map<String, Object>> columnList = new ArrayList<Map<String, Object>>();

        for (A1TemplateColumn column : a1TemplateColumnList1) {
            int canAdd = column.getCanAdd() == null ? 0 : column.getCanAdd().intValue();
            int deleteFlag = column.getDeleteFlag() == null ? 0 : column.getDeleteFlag().intValue();
            if (canAdd != 1 || deleteFlag != 0) continue;

            String columnName = column.getColumnName();
            //System.out.println("--columnName:"+columnName);
            if (json.optJSONArray(columnName) == null) {
                continue;
            }
            Object value = json.optJSONArray(columnName).get(0).toString().trim();


            //转换 data_transfer
            //取列的模板对象
            A1TemplateColumn a1TemplateColumn = a1TemplateColumnMap.get(columnName);
            if (a1TemplateColumn != null) {
                String dataTransfer = a1TemplateColumn.getDataTransfer();
                //金额转换
                if ("fen2yuan".equalsIgnoreCase(dataTransfer)) {
                    value = FenAndYuan.yuan2fenStr(value.toString());
                }
                // 页面上把日期转成字符串发过来了
				/*if ("date".equals(a1TemplateColumn.getColumnType()) || "date-range".equals(a1TemplateColumn.getColumnType())){
					value = DateTool.date2String(new Date(value);
				}*/
            }

            Map<String, Object> map = new HashMap<String, Object>();
            map.put(Column_Constant.COLUMN_NAME, columnName);
            map.put(Column_Constant.COLUMN_VALUE, value);
            map.put(Column_Constant.DATA_TYPE, column.getColumnDataType());
            columnList.add(map);
        }

        return columnList;
    }

    /**
     * 自动加载模板列
     *
     * @param _id
     * @return
     */
    @Override
    public Result loadColumn(String _id) {
        Result resultObj = new Result();
        Long id = Long.parseLong(_id);
        A1TemplateTable a1TemplateTable = new A1TemplateTable();
        a1TemplateTable.setId(id);
        Optional<A1TemplateTable> optionalA1TemplateTable = a1TemplateTableDao.findOne(Example.of(a1TemplateTable));
        A1TemplateTable templateTable = optionalA1TemplateTable.get();
        //模板中所有列
        List<String> tempColList = new ArrayList<String>();
        for (A1TemplateColumn tmpCol : templateTable.getA1TemplateColumnList()) {
            tempColList.add(tmpCol.getColumnName());
        }
        resultObj = loadColumn4DynamicDS(templateTable, tempColList);
        return resultObj;
    }

    private String parseColumnShowName(Map<String, Object> columnMap) {
        String columnName = (String) columnMap.get("name");
        String columnComment = (String) columnMap.get("column_comment");
        String property = FmUtils.switch2Property(columnName);
        if (StringUtils.isNull(columnComment)) {
            return property;
        }
        if (!columnComment.contains(";")) {
            return property;
        }
        columnComment = columnComment.trim();
        return columnComment.split(";")[0];
    }

    @Override
    public Result loadColumn4DynamicDS(A1TemplateTable templateTable, List<String> tempColList) {
        Result resultObj = new Result();

        //取PrimaryKey
        String pKey = templateDao.loadTablePrimaryKey(templateTable);

        // a2user t1 left join a2dept t2 on t1.dept_id=t2.id left join a2company t3 on t2.company_id=t3.id
        String tableNameSql = templateTable.getTableNameSql();
        // 取多对一查询时的所有表名
        List<String> tableNameList = new ArrayList<>();
        List<String> tableAliasList = new ArrayList<>();
        FmUtils.parseTableNameSql(tableNameSql, tableNameList, tableAliasList);
        // 存放整理好的模板列，最后存库用
        List<A1TemplateColumn> a1TemplateColumnList = new ArrayList<>();
        for (int i = 0; i < tableNameList.size(); i++) {
            String tableName = tableNameList.get(i);
            String tableAlias = tableAliasList.get(i);
            //自动给表增加通用字段（id, deleteflag, createdate ...），如果不存在话
            autoAddColumns(tableName);
            //取列List
            List<Map<String, Object>> list = templateDao.loadColumn(tableName);
            for (Map<String, Object> columnMap : list) {
                String columnNameBkup = (String) columnMap.get("name");
                String comment = (String) columnMap.get("column_comment");
                String columnNameSql = "";
                String data_type = (String) columnMap.get("data_type");

                // 多表查询时，非主表列名转换
                String columnName = columnNameBkup;
                if (!tableAlias.equalsIgnoreCase("t1")) {
                    columnName = tableName + "_" + columnNameBkup;
                    columnNameSql = tableAlias + "." + columnNameBkup;
                }
                //列是否存在模板列中了
                if (tempColList.contains(columnName)) continue;

                //添加模板列
                A1TemplateColumn templateColumn = new A1TemplateColumn();
                //templateColumn.setColumnShowName(FmUtils.switch2Property(columnName));
                templateColumn.setColumnShowName(parseColumnShowName(columnMap));
                templateColumn.setComment(comment);
                templateColumn.setColumnName(columnName);
                templateColumn.setColumnNameSql(columnNameSql); // t1.name as name
                templateColumn.setTableAliasName(tableAlias); // 对应表别名

                templateColumn.setA1TemplateTable(templateTable);
                templateColumn.setCanAdd(1);
                templateColumn.setIsHidden(0);
                templateColumn.setIsQuery(1);
                // type: input/date/date-range/num-range/select/tree/
                templateColumn.setColumnType("input");

                // primary key 不可以add, 系统自动生成
                if (columnName.equals(pKey)) {
                    templateColumn.setColumnShowName("ID");
                    templateColumn.setCanAdd(0);
                }
                //创建日期
                if (columnName.endsWith("create_time")) {
                    templateColumn.setColumnType("date");
                    templateColumn.setColumnShowName("创建日期");
                    templateColumn.setCanAdd(0);
                    templateColumn.setIsQuery(1);
                    templateColumn.setQueryType("between");
                    templateColumn.setColumnType("date-range");
                }
                //修改日期
                if (columnName.endsWith("update_time")) {
                    templateColumn.setColumnType("date");
                    templateColumn.setColumnShowName("修改日期");
                    templateColumn.setCanAdd(0);
                    templateColumn.setIsQuery(0);
                    templateColumn.setQueryType("between");
                    templateColumn.setColumnType("date-range");
                }
                //创建人
                if (columnName.endsWith("creator")) {
                    templateColumn.setColumnShowName("创建人");
                    templateColumn.setCanAdd(0);
                    templateColumn.setIsQuery(0);
                    templateColumn.setQueryType("like");
                }
                //创建人ID
                if (columnName.endsWith("creator_id")) {
                    templateColumn.setColumnShowName("创建人ID");
                    templateColumn.setCanAdd(0);
                    templateColumn.setIsQuery(0);
                    templateColumn.setIsHidden(1);
                }
                //公司ID
                if (columnName.endsWith("company_id")) {
                    templateColumn.setColumnShowName("公司ID");
                    templateColumn.setCanAdd(0);
                    templateColumn.setIsQuery(0);
                    templateColumn.setIsHidden(1);
                }

                //修改人
                if (columnName.equalsIgnoreCase("updator")) {
                    templateColumn.setColumnShowName("修改人");
                    templateColumn.setCanAdd(0);
                    templateColumn.setIsQuery(0);
                }
                //修改人ID
                if (columnName.equalsIgnoreCase("updator_id")) {
                    templateColumn.setColumnShowName("修改人ID");
                    templateColumn.setCanAdd(0);
                    templateColumn.setIsQuery(0);
                    templateColumn.setIsHidden(1);
                }
                //删除标志
                if (columnName.equalsIgnoreCase("delete_flag")) {
                    templateColumn.setColumnShowName("删除标志");
                    templateColumn.setColumnType("combobox");
                    templateColumn.setDefaultValue("0");
                    templateColumn.setColumnType("select");
                    templateColumn.setCanAdd(0);
                    templateColumn.setIsQuery(0);
                    templateColumn.setIsHidden(1);
                    templateColumn.setDataResource(1);
                }
                //version
                if (columnName.equalsIgnoreCase("version")) {
                    templateColumn.setColumnShowName("版本");
                    templateColumn.setCanAdd(0);
                    templateColumn.setIsQuery(0);
                    templateColumn.setIsHidden(1);
                }

                // 多表查询时，非主表列名转换
                if (!tableAlias.equalsIgnoreCase("t1")) {
                    templateColumn.setCanAdd(0);
                    templateColumn.setIsQuery(0);
                }

                templateColumn.setColumnDataType(data_type);//字段类型
                templateColumn.setDeleteFlag(0);
                templateColumn.setNeedSum(0);
                templateColumn.setSort(0);
                templateColumn.setColumnWidth(150);
                templateColumn.setIsFrozenColumn(0);
                templateColumn.setCreateDate(DateTool.getCurTimestamp());
                a1TemplateColumnList.add(templateColumn);
            }
        }

        //添加到模板表里
        for (A1TemplateColumn templateColumn : a1TemplateColumnList) {
            a1TemplateColumnDao.save(templateColumn);
        }

        return resultObj.success();
    }


    /**
     * 自动给表增加通用字段（id, deleteflag, createdate ...），如果不存在话
     * id  deleteflag createdate modifydate createman createman_id  modifyman modifyman_id  company_id
     *
     * @param tableName
     */
    private void autoAddColumns(String tableName) {
        // 对应字段的alter sql
        Map<String, List> sqlMap = null;
        List<String> sqlList = null;
        sqlMap = new LinkedHashMap<>();
        // id
        sqlList = new ArrayList<>();
        sqlList.add("alter table " + tableName + " add column id bigint(20) not null AUTO_INCREMENT primary key comment 'ID;模板：主键' first ");
        sqlMap.put("id", sqlList);

        // delete_flag
        sqlList = new ArrayList<>();
        sqlList.add("alter table " + tableName + " add column delete_flag bigint(20) not null default 0 comment '是否删除;模板：逻辑删除标志：1:true，0:false'");
        sqlList.add("alter table " + tableName + " add index idx_delete_flag(delete_flag); ");
        sqlMap.put("delete_flag", sqlList);

        // version
        sqlList = new ArrayList<>();
        sqlList.add("alter table " + tableName + " add column version int(5) not null default 0 comment '版本号;乐观锁版本号'");
        sqlMap.put("version", sqlList);

        // create_time
        sqlList = new ArrayList<>();
        sqlList.add("alter table " + tableName + " add column create_time timestamp not null DEFAULT current_timestamp() comment '创建时间;'");
        sqlList.add("alter table " + tableName + " add index idx_create_time(create_time); ");
        sqlMap.put("create_time", sqlList);
        // creator
        sqlList = new ArrayList<>();
        sqlList.add("alter table " + tableName + " add column creator varchar(100) NULL comment '创建人姓名;'");
        sqlMap.put("creator", sqlList);
        // creator_id
        sqlList = new ArrayList<>();
        sqlList.add("alter table " + tableName + " add column creator_id bigint(20) not null default 0 comment '创建人ID;'");
        sqlList.add("alter table " + tableName + " add index idx_creator_id(creator_id); ");
        sqlMap.put("creator_id", sqlList);

        // update_time
        sqlList = new ArrayList<>();
        sqlList.add("alter table " + tableName + " add column update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间;'");
        sqlMap.put("update_time", sqlList);
        // updator
        sqlList = new ArrayList<>();
        sqlList.add("alter table " + tableName + " add column updator varchar(100) NULL  comment '更新人;'");
        sqlMap.put("updator", sqlList);
        // updator_id
        sqlList = new ArrayList<>();
        sqlList.add("alter table " + tableName + " add column updator_id bigint(20) not null default 0 comment '更新人ID;'");
        sqlMap.put("updator_id", sqlList);

        // company_id
        sqlList = new ArrayList<>();
        sqlList.add("alter table " + tableName + " add column company_id bigint(20) not null default 0 comment '公司ID;模板：创建人组织或公司ID，数据权限控制用，主要应用SAAS平台'");
        sqlList.add("alter table " + tableName + " add index idx_company_id(company_id); ");
        sqlMap.put("company_id", sqlList);


        //取列List
        List<String> currentColNameList = new ArrayList<>();
        List<Map<String, Object>> columnList = templateDao.loadColumn(tableName);
        for (Map<String, Object> columnMap : columnList) {
            String columnName = (String) columnMap.get("name");
            currentColNameList.add(columnName);
        }

        // check and execute
        for (String commonColName : sqlMap.keySet()) {
            if (!currentColNameList.contains(commonColName)) {
                List<String> sqlList1 = sqlMap.get(commonColName);
                for (String sql : sqlList1) {
                    System.out.println(sql);
                    templateDao.execSql(sql);
                }
            }
        }
    }

    /**
     * 扫库自动配置生成源码
     *
     * @param mistTablename
     * @return
     */
    @Override
    public Result autocodebuilder(String mistTablename) {
        Result resultObj = new Result();
        //通过 mist tablename 找到所有符合的表名
        resultObj = templateDao.queryTablenamesByMistTablename(mistTablename);
        return resultObj;
    }


    /**
     * 查询option数据字典列表
     *
     * @param example
     * @return
     */
    @Override
    public List<A1TemplateColumnOptionDataDto> queryOptionDataListBySearch(Example example) {
        List<A1TemplateColumnOptionDataDto> dtoList = new ArrayList<>();
/*
		List<A1TemplateColumnOptionData> entityList =  optionDataDao.findAll(example, Sort.by("sort").ascending());
		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateColumnOptionDataDto.class);
		assembler.assembleDtos(dtoList, entityList, null, null);
*/

        return dtoList;
    }

    /**
     * 查询数据字典，新版本
     *
     * @param id
     * @return
     */
    @Override
    public TmptData queryTmptDataById(Long id) {
        TmptData tmptData = tmptDataMapper.mySelectById(id);
        return tmptData;
    }


}
